/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package baseDatos;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import AsocUsu.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;

/**
 *
 * @author oracle
 */
public class ConsultasBD extends BaseDeDatos{

    private ResultSet resultado = null;
    private Statement sentencia = null;

    public ConsultasBD() {
        super.ConectarBD();
    }

    public boolean Login(String usuario, String passwd) {
        boolean esta = false;
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("select * from Bancosol.Login where usuario='" + usuario + "' and pass='" + passwd + "'");
                while (resultado.next())
                    esta = true;
                this.DesconectarBD();
                return esta;
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                return false;
            }
        } else {
            this.DesconectarBD();
            return false;
        }
    }

    public void cambiarLogin(String cifAnt, String usuario, String passwd) {
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("UPDATE BANCOSOL.LOGIN SET " +
                        "USUARIO='"+ usuario +"', PASS='"+ passwd +"' WHERE USUARIO='"+ cifAnt +"'");
                this.DesconectarBD();
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
            }
        } else
            this.DesconectarBD();
    }

    public void eliminarLogin(String cif){
        if (this.ConectarBD()){
            try{
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("DELETE FROM BANCOSOL.LOGIN WHERE USUARIO='" + cif + "'");
                this.DesconectarBD();
            }catch (SQLException ex){
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                System.out.println(ex);
                this.DesconectarBD();
            }
        }
    }

    public void anadirFechaCreacion (String fechaCreacion){
        if (this.ConectarBD()){
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("INSERT INTO BANCOSOL.COPIAS_SEGURIDAD VALUES ('" + fechaCreacion + "')");
                this.DesconectarBD();
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                System.out.println("Error - anadirFechaCreacion: " + ex);
                this.DesconectarBD();
            }
        }
    }

    public String consultarUltimaCopia (){
        if (this.ConectarBD()){
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("SELECT to_char(FECHA_CREACION, 'dd/MM/yyyy') FROM BANCOSOL.COPIAS_SEGURIDAD ORDER BY FECHA_CREACION DESC");
                if (resultado.next()){
                    return resultado.getString(1);
                }
                this.DesconectarBD();
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println("Error - consultarUltimaCopia: " + ex);
                return null;
            }
        }
        return null;
    }

    public void anadirAsoc(Asociacion a, String pass) throws SQLException{
        if (this.ConectarBD()) {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("INSERT INTO BANCOSOL.ASOCIACION " +
                        "VALUES ('" + a.cif() +"', '"+ a.nombre() +"',"+ a.telefono() +" , '"+ a.correo() +"')");
                resultado = sentencia.executeQuery("INSERT INTO BANCOSOL.LOGIN VALUES ('"+ a.cif() +"', '"+ pass +"') ");
                this.DesconectarBD();
        } else {
            this.DesconectarBD();
        }
    }

    public void editarAsoc(String cifAnt, Asociacion aNueva) throws SQLException{
        if (this.ConectarBD()) {
            sentencia = this.conexion.createStatement();
            resultado = sentencia.executeQuery("UPDATE BANCOSOL.ASOCIACION SET " +
                    "CIF='"+ aNueva.cif() +"', NOMBRE='"+ aNueva.nombre() + "', " +
                    "TELEFONO="+ aNueva.telefono()+ ", CORREO='"+ aNueva.correo() +
                    "' WHERE CIF='"+ cifAnt +"'");
            this.DesconectarBD();
        } else {
            this.DesconectarBD();
        }
    }

    public void eliminarAsoc(String cif) {
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("DELETE FROM BANCOSOL.ASOCIACION WHERE CIF='"+ cif +"'");
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            this.DesconectarBD();
        }
    }

    public Asociacion consultarAsoc(String cif) {
        if (this.ConectarBD()){
            try {
                Asociacion a = new Asociacion();
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("SELECT * FROM BANCOSOL.ASOCIACION WHERE CIF='" + cif + "'");
                while (resultado.next()) {
                    a = new Asociacion(resultado.getString(1),
                            resultado.getString(2), resultado.getInt(3),
                            resultado.getString(4));
                }
                return a;
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println(ex);
                return null;
            }
        } else {
            this.DesconectarBD();
            return null;
        }
    }

    public String consultarAsocCIF(String cif) {
        if (this.ConectarBD()){
            try {
                String a = null;
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("SELECT * FROM BANCOSOL.ASOCIACION WHERE CIF='" + cif + "'");
                while (resultado.next()) {
                    a = resultado.getString(2);
                }
                return a;
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                return null;
            }
        } else {
            this.DesconectarBD();
            return null;
        }
    }

    public void anadirUsuario(Usuario u) throws SQLException{
        if (this.ConectarBD()) {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("INSERT INTO BANCOSOL.USUARIO " +
                        "VALUES ('" + u.nExpediente() +"', '"+ u.dni() +"', '" + u.nombre() +
                        "', '"+ u.apellidos() +"', '"+ u.tarjResidente() +"', '"+ u.nacionalidad() +
                        "', '"+ u.fechaNac() +"', '"+ u.tipoVia()+"', '"+ u.nombreVia()+
                        "', "+ u.numeroVia() +", '"+ u.ciudad() +"', '"+ u.provincia() +
                        "', "+ u.cP() +", '"+ u.asociacion() +"')");
                this.DesconectarBD();
        } else {
            this.DesconectarBD();
        }
    }

    public void editarUsuario(String nExp, Usuario uNuevo) throws SQLException {
        if (this.ConectarBD()) {
            sentencia = this.conexion.createStatement();
            resultado = sentencia.executeQuery("UPDATE BANCOSOL.USUARIO SET " +
                    "N_EXPEDIENTE='" + uNuevo.nExpediente() +"', DNI='"+ uNuevo.dni() +"', NOMBRE='"+ uNuevo.nombre() +
                    "', APELLIDOS='"+ uNuevo.apellidos() +"', TARJETA_RESIDENTE='"+ uNuevo.tarjResidente() +"', NACIONALIDAD='"+ uNuevo.nacionalidad() +
                    "', FECHA_NAC='"+ uNuevo.fechaNac() +"', TIPO_VIA='"+ uNuevo.tipoVia()+"', NOMBRE_VIA='"+ uNuevo.nombreVia()+
                    "', NUMERO_VIA="+ uNuevo.numeroVia() +", CIUDAD='"+ uNuevo.ciudad() +"', PROVINCIA='"+ uNuevo.provincia() +
                    "', CP="+ uNuevo.cP() + ", ASOCIACION='"+ uNuevo.asociacion()+"' WHERE N_EXPEDIENTE='"+ nExp +"'");
            this.DesconectarBD();
        } else {
            this.DesconectarBD();
        }
    }

    public void eliminarUsuario(String dni) throws SQLException {
        if (this.ConectarBD()) {
            sentencia = this.conexion.createStatement();
            resultado = sentencia.executeQuery("DELETE FROM BANCOSOL.USUARIO WHERE DNI='"+ dni +"'");
            this.DesconectarBD();
        } else {
            this.DesconectarBD();
        }
    }

    public void eliminarUsuarioPorAsoc(String cif){
        if(this.ConectarBD()){
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("DELETE FROM BANCOSOL.USUARIO WHERE ASOCIACION='" + cif + "'");
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                System.out.println(ex);
            }
        }
    }

    public Usuario consultarUsuario(String dni) {
        if (this.ConectarBD()) {
            try {
                Usuario u = null;
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("SELECT N_EXPEDIENTE, DNI, NOMBRE, APELLIDOS, TARJETA_RESIDENTE, " +
                        "NACIONALIDAD, to_char(FECHA_NAC, 'dd/MM/yyyy'), TIPO_VIA, NOMBRE_VIA, NUMERO_VIA, CIUDAD, " +
                        "PROVINCIA, CP, ASOCIACION FROM BANCOSOL.USUARIO WHERE DNI='"+ dni +"'");
                while (resultado.next()) {
                    u = new Usuario(resultado.getString(1), resultado.getString(2),
                            resultado.getString(3), resultado.getString(4),
                            resultado.getString(5), resultado.getString(6),
                            resultado.getString(7), resultado.getString(8),
                            resultado.getString(9), resultado.getInt(10),
                            resultado.getString(11), resultado.getString(12),
                            resultado.getInt(13), resultado.getString(14));
                }
                this.DesconectarBD();
                return u;
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                return null;
            }
        } else {
            this.DesconectarBD();
            return null;
        }
    }

    public void anadirUnidadFamiliar(UnidadFamiliar uF) throws SQLException {
        if (this.ConectarBD()) {
            sentencia = this.conexion.createStatement();
            resultado = sentencia.executeQuery("INSERT INTO BANCOSOL.UNIDAD_FAMILIAR VALUES (" +
                    "'"+ uF.nExpediente() +"', '"+ uF.identificativo() +"', '"+ uF.parentesco() +
                    "', '"+ uF.nombre() +"', '"+ uF.apellidos() +"', '"+ uF.dni() +"')");
            this.DesconectarBD();
        } else
            this.DesconectarBD();
    }

    public void editarUnidadFamiliar(String dniAnt, UnidadFamiliar uNueva) {
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("UPDATE BANCOSOL.UNIDAD_FAMILIAR SET " +
                        "PARENTESCO='"+ uNueva.parentesco() +"', NOMBRE='"+ uNueva.nombre() +"'," +
                        "APELLIDOS='"+ uNueva.apellidos() +"', DNI='"+ uNueva.dni() +"', " +
                        "N_EXPEDIENTE='" + uNueva.nExpediente() + "'WHERE DNI='" + dniAnt +"'");
                this.DesconectarBD();
            } catch (SQLException ex) {
                this.DesconectarBD();
                System.out.println("Error: editarUnidadFamiliar - " + ex);
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else
            this.DesconectarBD();
    }

    public void editarExpUnidadFamiliar(String nExpAnt, String nExpNuevo){
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("UPDATE BANCOSOL.UNIDAD_FAMILIAR SET " +
                        "N_EXPEDIENTE='" + nExpNuevo + "'WHERE N_EXPEDIENTE='" + nExpAnt +"'");
                this.DesconectarBD();
            } catch (SQLException ex) {
                this.DesconectarBD();
                System.out.println("Error: editarExpUnidadFamiliar - " + ex);
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else
            this.DesconectarBD();
    }

    public void eliminarUnidadFamiliar(UnidadFamiliar uF) {
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("DELETE FROM BANCOSOL.UNIDAD_FAMILIAR " +
                        "WHERE N_EXPEDIENTE='"+ uF.nExpediente() +"' AND IDENTIFICATIVO='"+ uF.identificativo() +"'");
                this.DesconectarBD();
            } catch (SQLException ex) {
                this.DesconectarBD();
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else
            this.DesconectarBD();
    }

    public void eliminarUnidadFamiliar (String nExp){
        if (this.ConectarBD()) {
            try {
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("DELETE FROM BANCOSOL.UNIDAD_FAMILIAR UF WHERE UF.N_EXPEDIENTE='" + nExp + "'");
                this.DesconectarBD();
            } catch (SQLException ex) {
                this.DesconectarBD();
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                System.out.println(ex);
            }
        } else
            this.DesconectarBD();
    }

    public UnidadFamiliar consultarUnidadFamiliar(String dni) {
        if (this.ConectarBD()) {
            try {
                UnidadFamiliar uF = null;
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("SELECT * FROM BANCOSOL.UNIDAD_FAMILIAR " +
                        "WHERE DNI='" + dni + "'");
                while (resultado.next()) {
                    uF = new UnidadFamiliar(resultado.getString(1), resultado.getString(2),
                            resultado.getString(3), resultado.getString(4),
                            resultado.getString(5), resultado.getString(6));
                }
                return uF;
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                return null;
            }
        } else {
            this.DesconectarBD();
            return null;
        }
    }

    public Set<Asociacion> listarAsociaciones(){
        SortedSet<Asociacion> lAsociaciones = new TreeSet<Asociacion>();
        if (this.ConectarBD()){
            try{
                sentencia = this.conexion.createStatement();
                resultado = sentencia.executeQuery("SELECT * FROM BANCOSOL.ASOCIACION");
                while (resultado.next()) {
                    lAsociaciones.add(new Asociacion(resultado.getString(1),
                            resultado.getString(2), resultado.getInt(3),
                            resultado.getString(4), numeroSocios(resultado.getString(1))));
                }
                return lAsociaciones;
            } catch (SQLException ex){
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                return null;
            }
        } else {
            this.DesconectarBD();
            return null;
        }
    }

    public int numeroSocios(String cif){
        int num = 0;
        if (this.ConectarBD()){
            try {
                Statement sen = this.conexion.createStatement();
                ResultSet res = sen.executeQuery("SELECT COUNT(*) FROM BANCOSOL.USUARIO WHERE ASOCIACION='" + cif + "'");
                while (res.next())
                    num += res.getInt(1);
                res = sen.executeQuery("select distinct count(*) from BANCOSOL.usuario u inner join BANCOSOL.UNIDAD_FAMILIAR uf on u.N_EXPEDIENTE = uf.N_EXPEDIENTE AND u.ASOCIACION='" + cif + "' AND uf.IDENTIFICATIVO <> '00'");
                while (res.next())
                    num += res.getInt(1);
                return num;
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println(ex);
            }
        }
        return 0;
    }

    public List<String> numSociosNacionalidad(String cif){
        LinkedList<String> lista = new LinkedList<String>();
        if (this.ConectarBD()){
            try {
                Statement sen = this.conexion.createStatement();
                ResultSet res = sen.executeQuery("SELECT DISTINCT U.NACIONALIDAD, COUNT(*) FROM BANCOSOL.USUARIO U WHERE U.ASOCIACION='" + cif + "' group by U.NACIONALIDAD ORDER BY U.NACIONALIDAD");
                while (res.next()){
                    lista.add(res.getString(1));
                    int numSocioNacio = res.getInt(2) + this.numFamNacionalidad(res.getString(1), cif);
                    lista.add(String.valueOf(numSocioNacio));
                }
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println(ex);
            }
        }
        return lista;
    }

    public int numFamNacionalidad(String nacionalidad, String cif){
        int num = 0;
        if (this.ConectarBD()){
            try {
                Statement sen = this.conexion.createStatement();
                ResultSet res = sen.executeQuery("SELECT COUNT(*) FROM BANCOSOL.USUARIO U JOIN BANCOSOL.UNIDAD_FAMILIAR UF ON U.N_EXPEDIENTE=UF.N_EXPEDIENTE AND UF.IDENTIFICATIVO <> '00' AND U.NACIONALIDAD='" + nacionalidad + "' AND U.ASOCIACION='" + cif + "' group by U.NACIONALIDAD");
                while (res.next()) {
                    num = res.getInt(1);
                }
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println(ex);
            }
        }
        return num;
    }

    public List<Usuario> listarUsuarios(){
        LinkedList<Usuario> lista = new LinkedList<Usuario>();
        if (this.ConectarBD()){
            try {
                Statement sen = this.conexion.createStatement();
                ResultSet res = sen.executeQuery("SELECT U.DNI  FROM BANCOSOL.USUARIO U ORDER BY U.ASOCIACION, U.NOMBRE");
                while (res.next())
                    lista.add(this.consultarUsuario(res.getString(1)));
                    
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println(ex);
            }
        }
        return lista;
    }

    public List<UnidadFamiliar> listarFamUsuario(String nExp){
        LinkedList<UnidadFamiliar> lista = new LinkedList<UnidadFamiliar>();
        if (this.ConectarBD()){
            try {
                Statement sen = this.conexion.createStatement();
                ResultSet res = sen.executeQuery("SELECT * FROM BANCOSOL.UNIDAD_FAMILIAR UF WHERE UF.N_EXPEDIENTE='" + nExp + "' AND UF.IDENTIFICATIVO <> '00' ORDER BY UF.IDENTIFICATIVO");
                while (res.next()){
                    lista.add(new UnidadFamiliar(res.getString(1), res.getString(2), res.getString(3), res.getString(4), res.getString(5), res.getString(6)));
                }
            } catch (SQLException ex) {
                Logger.getLogger(ConsultasBD.class.getName()).log(Level.SEVERE, null, ex);
                this.DesconectarBD();
                System.out.println(ex);
            }
        }
        return lista;
    }
}
